﻿/**********************************************************************
*创建人：蔡水明
*创建时间：2018/2/10 10:48:34
*说明：<Function>
/**********************************************************************/
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;

namespace Utilities
{
    /// <summary>
    /// 采用NPOI进行Excel操作的类
    /// </summary>
    public class NpoiExcel : IDisposable
    {
        #region 属性
        private string fileName = null; //文件名
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;
        public ISheet Sheet = null;
        /// <summary>
        /// 错误信息，比如Excel文件打开失败
        /// </summary>
        public string errorMsg = string.Empty;
        #endregion
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="fileName">文件名</param>
        public NpoiExcel(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
            if (File.Exists(fileName))
            {
                try
                {
                    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook(fs);
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook(fs);
                    Sheet = workbook.GetSheetAt(0);

                }
                catch (Exception ex)
                {
                    errorMsg = ex.Message;
                    LogHelper.Error(ex);
                }
            }
            else
            {
                workbook = new HSSFWorkbook();
                Sheet = workbook.CreateSheet("Sheet1");
            }
        }
        /// <summary>
        /// 不带文件名称的构造函数
        /// </summary>
        public NpoiExcel()
        {
            workbook = new HSSFWorkbook();
            Sheet = workbook.CreateSheet("Sheet1");
        }
        /// <summary>
        /// 创建一个工作表
        /// </summary>
        /// <param name="sName"></param>
        public void CreateSheet(string sName)
        {
            Sheet = workbook.CreateSheet(sName);
        }
        /// <summary>
        /// 第一行的最后一列
        /// </summary>
        /// <returns></returns>
        public int LastColumn(int SheetNum)
        {
            ISheet St = workbook.GetSheetAt(SheetNum);
            IRow firstRow = St.GetRow(0);
            return firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数            
        }
        /// <summary>
        /// 工作表的最后一行
        /// </summary>
        /// <param name="St"></param>
        /// <returns></returns>
        public int LastRow(int SheetNum)
        {
            ISheet St = workbook.GetSheetAt(SheetNum);
            return St.LastRowNum;
        }
        /// <summary>
        /// 设置当前工作表sheet
        /// </summary>
        /// <param name="Index">工作表的顺序</param>
        public void setSheetByIndex(int Index)
        {
            Sheet = workbook.GetSheetAt(index: Index);
            if (Sheet == null)
            {
                errorMsg = "工作表名称错误！";
                LogHelper.Error("空白工作表" + Index.ToString());
            }
        }

        /// <summary>
        /// 设置当前工作表sheet
        /// </summary>
        /// <param name="sheetName">工作表的名称</param>
        public void setSheetByName(string sheetName)
        {
            Sheet = workbook.GetSheet(sheetName);
            if (Sheet == null)
                errorMsg = "工作表名称错误！";
        }
        public void SetSheetName(int sIndex, string sName)
        {
            workbook.SetSheetName(sIndex, sName);
        }
        public void delSheetByName(string sn)
        {
            Sheet = workbook.GetSheet(sn);
            workbook.RemoveSheetAt(workbook.GetSheetIndex(sn));
        }
        /// <summary>
        /// 删除不在清单中的工作表
        /// </summary>
        /// <param name="sourceName"></param>
        public void DelSheetList(List<string> Lst)
        {
            if (Lst.Count == 0) return;
            for (int i = workbook.NumberOfSheets - 1; i >= 0; i--)
            {
                ISheet curSheet = workbook.GetSheetAt(i);
                if (!Lst.Contains(curSheet.SheetName))
                {
                    workbook.RemoveSheetAt(i);
                }
            }
        }
        /// <summary>
        /// 设置单元格的值(默认取Excel文件第一个Sheet)
        /// </summary>       
        /// <param name="rowIndex">行号</param>
        /// <param name="colIndex">列号</param>
        /// <param name="cellValue">值</param>
        public void setCellValue(int rowIndex, int colIndex, object cellValue)
        {
            ICell cell = getCell(rowIndex, colIndex);
            if (cell == null) return;
            if (cellValue is string)
            {
                //double tp = 0;
                //if (double.TryParse(cellValue.ToString(), out tp))
                //    cell.SetCellValue(tp);
                //else
                cell.SetCellValue(cellValue.ToString());
                return;
            }
            if (cellValue is bool)
            {
                cell.SetCellValue((bool)cellValue);
                return;
            }
            if (cellValue is DateTime)
            {
                cell.SetCellValue((DateTime)cellValue);
                return;
            }
            if (cellValue is double)
            {
                cell.SetCellValue(Convert.ToDouble(cellValue));
                return;
            }
            if (cellValue is decimal)
            {
                cell.SetCellValue($"{cellValue}");
                return;
            }
            if (cellValue is int)
            {
                cell.SetCellValue((int)cellValue);
                return;
            }
            if (cellValue is IRichTextString)
            {
                cell.SetCellValue((IRichTextString)cellValue);
                return;
            }
        }
        /// <summary>
        /// 设置单元格公式
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="formula"></param>
        public void setCellFormula(int rowIndex, int colIndex, string formula)
        {
            ICell cell = getCell(rowIndex, colIndex);
            cell.SetCellFormula(formula);
        }
        /// <summary>
        /// 刷新公式
        /// </summary>
        public void RecountFormula()
        {
            if (workbook == null) return;
            workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
        }
        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="rowIndex">行号</param>
        /// <param name="colIndex">列号</param>
        /// <returns></returns>
        public object getCellValue(int rowIndex, int colIndex)
        {
            ICell cell = getCell(rowIndex, colIndex);
            if (cell == null) return null;
            object result = null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    break;
                case CellType.Boolean:
                    result = cell.BooleanCellValue;
                    break;
                case CellType.Error:
                    result = cell.ErrorCellValue;
                    break;
                case CellType.Formula:
                    result = cell.CellFormula;
                    break;
                case CellType.Numeric:
                    result = cell.NumericCellValue;
                    break;
                case CellType.String:
                    result = cell.StringCellValue;
                    break;
                case CellType.Unknown:
                    break;
                default:
                    break;
            }
            return result;
        }
        /// <summary>
        /// 把图片插入到Excel中
        /// </summary>
        /// <param name="imgFileName"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        public void insertImg(string imgFileName, int rowIndex, int colIndex)
        {
            if (Sheet == null) return;
            string ext = Path.GetExtension(imgFileName).ToLower();
            byte[] bytes = System.IO.File.ReadAllBytes(imgFileName);
            int pictureIdx = -1;
            switch (ext)
            {
                case ".jpg":
                case ".jpeg":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
                    break;
                case ".bmp":
                case ".dib":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.DIB);
                    break;
                case ".emf":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.EMF);
                    break;
                case ".pict":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.PICT);
                    break;
                case ".png":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
                    break;
                case ".wmf":
                    pictureIdx = workbook.AddPicture(bytes, PictureType.WMF);
                    break;
                default:
                    break;
            }
            //将图片文件读入一个字符串
            if (pictureIdx > 0)
            {
                HSSFPatriarch patriarch = (HSSFPatriarch)Sheet.CreateDrawingPatriarch();
                // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
                int ColSpan = 0;
                int endCol = colIndex + 1;
                if (isMergeCell(rowIndex, colIndex, out ColSpan))
                    endCol = endCol + ColSpan - 1;
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, colIndex, rowIndex, endCol, rowIndex + 1);
                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            }
        }
        /// <summary>
        /// 把图片插入到Excel中
        /// </summary>
        /// <param name="pic"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        public void insertImg2(string pic, int rowIndex, int colIndex)
        {
            if (Sheet == null) return;
            //string ext = Path.GetExtension(imgFileName).ToLower();
            byte[] bytes = Convert.FromBase64String(pic);
            int pictureIdx = -1;

            pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);


            //将图片文件读入一个字符串
            if (pictureIdx > 0)
            {
                HSSFPatriarch patriarch = (HSSFPatriarch)Sheet.CreateDrawingPatriarch();
                // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, colIndex, rowIndex, colIndex + 1, rowIndex + 1);
                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            }
        }
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            //  ISheet sheet = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    Sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = Sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = Sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="NoEmptyCol">一定不会空白的列号（判断是否空数据的依据）</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable2(string sheetName, bool isFirstRowColumn, int NoEmptyCol)
        {
            if (!string.IsNullOrEmpty(sheetName))
            {
                Sheet = workbook.GetSheet(sheetName);
            }
            if (Sheet == null) return null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {

                IRow firstRow = Sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                if (NoEmptyCol >= cellCount) return null;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            if (cell.CellType.ToString() == "Numeric")
                            {
                                double cellValue = cell.NumericCellValue;
                                if (cellValue > 0)
                                {
                                    DataColumn column = new DataColumn(cellValue.ToString());
                                    data.Columns.Add(column);
                                }
                            }
                            else
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                    }
                    startRow = Sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = Sheet.FirstRowNum;
                }

                try
                {
                    //最后一列的标号                    
                    int rowCount = Sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = Sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　
                        if (string.IsNullOrEmpty(row.GetCell(NoEmptyCol).ToString())) continue;
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                            else
                            {
                                continue;
                            }

                        }
                        data.Rows.Add(dataRow);
                    }
                }
                catch (Exception ex)
                {

                    Console.WriteLine("数据读取: " + ex.Message);
                }
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="NoEmptyCol">一定不会空白的列号（判断是否空数据的依据）</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int NoEmptyCol, System.Windows.Forms.ToolStripProgressBar tsBar)
        {
            if (!string.IsNullOrEmpty(sheetName))
            {
                Sheet = workbook.GetSheet(sheetName);
            }
            if (Sheet == null) return null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                IRow firstRow = Sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                tsBar.Maximum = Sheet.LastRowNum + cellCount;
                tsBar.Step = 1;
                if (NoEmptyCol >= cellCount) return null;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        tsBar.PerformStep();
                        System.Windows.Forms.Application.DoEvents();
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            if (cell.CellType.ToString() == "Numeric")
                            {
                                double cellValue = cell.NumericCellValue;
                                if (cellValue > 0)
                                {
                                    DataColumn column = new DataColumn(cellValue.ToString());
                                    data.Columns.Add(column);
                                }
                            }
                            else
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                    }
                    startRow = Sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = Sheet.FirstRowNum;
                }

                try
                {
                    //最后一列的标号                    
                    int rowCount = Sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        tsBar.PerformStep();
                        System.Windows.Forms.Application.DoEvents();
                        IRow row = Sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　
                        if (string.IsNullOrEmpty(row.GetCell(NoEmptyCol).ToString())) continue;
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                            else
                            {
                                continue;
                            }

                        }
                        data.Rows.Add(dataRow);
                    }
                }
                catch (Exception ex)
                {

                    Console.WriteLine("数据读取: " + ex.Message);
                }
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 保存
        /// </summary>
        public void Save()
        {
            if (!string.IsNullOrEmpty(fileName))
            {
                using (fs = File.OpenWrite(fileName))
                {
                    workbook.Write(fs);
                }
            }
            else
            {
                errorMsg = "没有要保存的文件名";
            }
        }
        /// <summary>
        /// 保存并退出
        /// </summary>
        public void SaveAndClose()
        {
            using (fs = File.OpenWrite(fileName))
            {
                workbook.Write(fs);
            }
            Close();
        }
        /// <summary>
        /// 另存为
        /// </summary>
        /// <param name="newFileName">新文件名</param>
        public void SaveAs(string newFileName)
        {
            if (workbook == null) return;
            using (fs = File.OpenWrite(newFileName))
            {
                workbook.Write(fs);
            }
            Close();
        }

        /// <summary>
        /// 关闭并释放资源
        /// </summary>
        public void Close()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
        /// <summary>
        /// 复制指定行并插入到指下一行
        /// </summary>
        /// <param name="wk"></param>
        /// <param name="sheetcount"></param>
        /// <param name="starRow"></param>
        /// <param name="rows"></param>
        public void InsertCopyRows(int starRow)
        {
            Sheet.ShiftRows(starRow + 1, Sheet.LastRowNum, 1, true, true);
            starRow = starRow - 1;
            HSSFRow sourceRow = null;
            HSSFRow targetRow = null;
            HSSFCell sourceCell = null;
            HSSFCell targetCell = null;
            starRow = starRow + 1;
            sourceRow = (HSSFRow)Sheet.GetRow(starRow);
            targetRow = (HSSFRow)Sheet.CreateRow(starRow + 1);
            targetRow.HeightInPoints = sourceRow.HeightInPoints;
            int m = 0;
            while (m < 20)
            {
                sourceCell = (HSSFCell)sourceRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                targetCell = (HSSFCell)targetRow.CreateCellFromRecord(sourceCell.CellValueRecord);
                int colSpan = 0;
                if (isMergeCell(starRow, m, out colSpan))//如果有合并单元格，新行也要做相应合并
                {
                    SetCellRangeAddress1(starRow + 1, starRow + 1, m, m + colSpan - 1);
                    m += colSpan;
                }
                else
                    m++;

                //targetCell.CellStyle = sourceCell.CellStyle;
                //targetCell.SetCellType(sourceCell.CellType);

            }
        }
        /// <summary>
        /// 插入一行
        /// </summary>
        /// <param name="rowIdx"></param>
        public void InsertRow(int rowIdx)
        {
            IRow targetRow = null;
            targetRow = Sheet.GetRow(rowIdx);
            if (targetRow != null)
            {
                Sheet.ShiftRows(rowIdx, Sheet.LastRowNum, 1);
            }
            targetRow = Sheet.CreateRow(rowIdx);
        }
        /// <summary>
        /// 插入一行（空白）
        /// </summary>
        /// <param name="rowIdx"></param>
        public void InsertBlankRow(int rowIdx, int colSpan, ICellStyle cellStyle = null, bool isFontBold = false, string fontName = "", short fontSize = 0)
        {
            IRow targetRow = Sheet.GetRow(rowIdx);
            if (targetRow != null)
            {
                Sheet.ShiftRows(rowIdx, Sheet.LastRowNum, 1);
            }

            targetRow = Sheet.CreateRow(rowIdx);
            ICell targetCell = null;
            int m = 0;
            while (m < colSpan)
            {
                targetCell = targetRow.GetCell(m);

                if (targetCell == null)
                {
                    targetCell = targetRow.CreateCell(m);
                }

                if (isFontBold || !string.IsNullOrEmpty(fontName) || fontSize > 0)
                {
                    IFont f = workbook.CreateFont();
                    if (isFontBold)
                    {
                        f.Boldweight = (short)FontBoldWeight.Bold;
                    }
                    if (!string.IsNullOrEmpty(fontName))
                    {
                        f.FontName = fontName;
                    }
                    if (fontSize > 0)
                    {
                        f.FontHeightInPoints = fontSize;
                    }
                    cellStyle.SetFont(f);
                }

                SetCellFrame(ref targetCell, cellStyle);
                m++;
            }

            CellRangeAddress region = SetCellRangeAddress1(rowIdx, rowIdx, 0, colSpan - 1);

            //for (int j = region.FirstColumn; j <= region.LastColumn; j++)
            //{
            //    ICell singleCell = HSSFCellUtil.GetCell(targetRow, (short) j);
            //    SetCellFrame(ref singleCell);
            //}
        }
        /// <summary>
        /// 复制当前行（格式不包括数据）并插入到目标行
        /// </summary>
        /// <param name="rowIdx"></param>
        /// <param name="targetRowIdx"></param>
        public void CopyRow(int rowIdx, int targetRowIdx)
        {
            IRow sourceRow = null;
            IRow targetRow = null;
            sourceRow = Sheet.GetRow(rowIdx);
            targetRow = Sheet.GetRow(targetRowIdx);
            if (targetRow != null)
            {
                Sheet.ShiftRows(targetRowIdx, Sheet.LastRowNum, 1,true, true);
            }
            targetRow = Sheet.CreateRow(targetRowIdx);

            targetRow.HeightInPoints = sourceRow.HeightInPoints;
            ICell sourceCell = null;
            ICell targetCell = null;
            int m = 0;
            while (m < Sheet.NumMergedRegions)
            {
                sourceCell = sourceRow.GetCell(m, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                targetCell = targetRow.CreateCell(m);
                SetCellFrame(sourceCell, ref targetCell);
                int colSpan = 0;
                if (isMergeCell(rowIdx, m, out colSpan))//如果有合并单元格，新行也要做相应合并
                {
                    int cs = 1;
                    do
                    {
                        targetCell = targetRow.CreateCell(m + cs);
                        SetCellFrame(ref targetCell);
                        //SetCellFrame(sourceCell, ref targetCell);
                    } while (cs++ < colSpan);

                    SetCellRangeAddress1(targetRowIdx, targetRowIdx, m, m + colSpan - 1);
                    m += colSpan;
                }
                else
                {
                    m++;
                }

                //targetCell.CellStyle = sourceCell.CellStyle;
                //targetCell.SetCellType(sourceCell.CellType);
            }
        }
        /// <summary>
        /// 设置格子外边框
        /// </summary>
        /// <param name="target"></param>
        public void SetCellFrame(ICell source, ref ICell target)
        {
            ICellStyle style = source.CellStyle;

            target.CellStyle = style;
        }
        /// <summary>
        /// 设置格子外边框
        /// </summary>
        /// <param name="target"></param>
        public void SetCellFrame(ref ICell target, ICellStyle style = null)
        {
            if(style == null) style = InitCellStyle();

            target.CellStyle = style;
        }
        public ICellStyle InitCellStyle(bool isTop = true, bool isLeft = true, bool isBottom = true, bool isRight = true)
        {
            ICellStyle style = workbook.CreateCellStyle();

            if (isTop)
            {
                style.BorderTop = BorderStyle.Thin;
                style.TopBorderColor = HSSFColor.Black.Index;
            }
            else
            {
                style.BorderTop = BorderStyle.None;
                style.TopBorderColor = HSSFColor.White.Index;
            }
            if (isLeft)
            {
                style.BorderLeft = BorderStyle.Thin;
                style.LeftBorderColor = HSSFColor.Black.Index;
            }
            else
            {
                style.BorderLeft = BorderStyle.None;
                style.LeftBorderColor = HSSFColor.White.Index;
            }
            if (isBottom)
            {
                style.BorderBottom = BorderStyle.Thin;
                style.BottomBorderColor = HSSFColor.Black.Index;
            }
            else
            {
                style.BorderBottom = BorderStyle.None;
                style.BottomBorderColor = HSSFColor.White.Index;
            }
            if (isRight)
            {
                style.BorderRight = BorderStyle.Thin;
                style.RightBorderColor = HSSFColor.Black.Index;
            }
            else
            {
                style.BorderRight = BorderStyle.None;
                style.RightBorderColor = HSSFColor.White.Index;
            }

            return style;
        }

        public void SetAlignCenter(ref ICell cell)
        {
            ICellStyle style = cell.CellStyle;

            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            cell.CellStyle = style;
        }

        //设置对齐：居中
        public void SetAlignCenter(int row, int col)
        {
            ICell cell = Sheet.GetRow(row).GetCell(col);
            SetAlignCenter(ref cell);
        }

        public bool isMergeCell(int rowIndex, int colIndex, out Point start, out Point end)
        {
            //定义返回结果
            bool result = false;
            start = new Point(0, 0);
            end = new Point(0, 0);
            //如果没有行数，返回false
            if ((rowIndex < 0) || (colIndex < 0)) return result;
            //获取合并单元格数
            int regionsCount = Sheet.NumMergedRegions;
            for (int i = 0; i < regionsCount; i++)
            {
                CellRangeAddress range = Sheet.GetMergedRegion(i);
                if (range == null) break;
                if (rowIndex >= range.FirstRow && rowIndex <= range.LastRow && colIndex >= range.FirstColumn && colIndex <= range.LastColumn)
                {
                    start = new Point(range.FirstRow, range.FirstColumn);
                    end = new Point(range.LastRow, range.LastColumn);
                    result = true;
                    break;
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单元格是否合并单元格已经列跨度
        /// </summary>
        /// <param name="rowNum"></param>
        /// <param name="colNum"></param>
        /// <param name="colSpan"></param>
        /// <returns></returns>
        public bool isMergeCell(int rowNum, int colNum, out int colSpan)
        {
            bool result = false;
            int rowSpan = 0;
            colSpan = 0;
            if ((rowNum < 0) || (colNum < 0)) return result;
            int rowIndex = rowNum;
            int colIndex = colNum;
            int regionsCount = Sheet.NumMergedRegions;
            rowSpan = 1;
            colSpan = 1;
            for (int i = 0; i < regionsCount; i++)
            {
                CellRangeAddress range = Sheet.GetMergedRegion(i);
                if (range == null) break;
                Sheet.IsMergedRegion(range);
                if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
                {
                    rowSpan = range.LastRow - range.FirstRow + 1;
                    colSpan = range.LastColumn - range.FirstColumn + 1;
                    break;
                }
            }
            return colSpan > 1 || rowSpan > 1;

        }

        #region 内部函数
        /// <summary>
        /// 根据行列号获取单元格
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <returns></returns>
        private ICell getCell(int rowIndex, int colIndex)
        {
            if (Sheet == null) return null;

            IRow row = Sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = Sheet.CreateRow(rowIndex);
            }
            ICell cell = row.GetCell(colIndex);
            if (cell == null)
            {
                cell = row.CreateCell(colIndex);
            }
            return cell;
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void SetCellRangeAddress(int rowstart, int rowend, int colstart, int colend)
        {
            //sheet = workbook.CreateSheet("Sheet1");
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            Sheet.AddMergedRegion(cellRangeAddress);
            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.Alignment = HorizontalAlignment.Center;
            //设置合并后style
            var cell = Sheet.GetRow(rowstart).GetCell(colstart);
            cell.CellStyle = cellstyle;
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public CellRangeAddress SetCellRangeAddress1(int rowstart, int rowend, int colstart, int colend)
        {
            //sheet = workbook.CreateSheet("Sheet1");
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            Sheet.AddMergedRegion(cellRangeAddress);
            return cellRangeAddress;
        }
        public void SetTopBorderAndCenter(int row, int col)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.TopBorderColor = HSSFColor.Black.Index;
            style.Alignment = HorizontalAlignment.Center;//【Center】居中  
            style.VerticalAlignment = VerticalAlignment.Center;
            var cell = Sheet.GetRow(row).GetCell(col);
            cell.CellStyle = style;
        }
        //设置右对齐
        public void SetAlignRight(int row, int col)
        {
            var cell = Sheet.GetRow(row).GetCell(col);

            ICellStyle style = cell.CellStyle;

            style.Alignment = HorizontalAlignment.Right;
            style.VerticalAlignment = VerticalAlignment.Center;
            cell.CellStyle = style;
        }
        //设置左对齐
        public void SetAlignLeft(int row, int col)
        {
            var cell = Sheet.GetRow(row).GetCell(col);

            ICellStyle style = cell.CellStyle;

            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;
            cell.CellStyle = style;
        }
        public void SetAllBorder(int startRow, int startCol, int endRow, int endCol)
        {
            for (int i = startRow; i <= endRow; i++)
            {
                for (int j = startCol; j <= endCol; j++)
                {

                    ICell cell = getCell(i, j);
                    int span = 0;
                    if (isMergeCell(i, j, out span))
                    {
                        ICell celstart = getCell(i, j);
                        ICellStyle style1 = workbook.CreateCellStyle();
                        style1.BorderBottom = BorderStyle.Thin;
                        style1.BottomBorderColor = HSSFColor.Black.Index;
                        style1.BorderLeft = BorderStyle.Thin;
                        style1.LeftBorderColor = HSSFColor.Black.Index;
                        style1.BorderTop = BorderStyle.Thin;
                        style1.TopBorderColor = HSSFColor.Black.Index;
                        cell.CellStyle = style1;
                        celstart.CellStyle = style1;

                        ICell cellend = getCell(i, j + span - 1);
                        ICellStyle style2 = workbook.CreateCellStyle();
                        style2.BorderBottom = BorderStyle.Thin;
                        style2.BottomBorderColor = HSSFColor.Black.Index;
                        style2.BorderTop = BorderStyle.Thin;
                        style2.TopBorderColor = HSSFColor.Black.Index;
                        style2.BorderRight = BorderStyle.Thin;
                        style2.RightBorderColor = HSSFColor.Black.Index;
                        cellend.CellStyle = style2;
                    }
                    else
                    {
                        ICellStyle style = workbook.CreateCellStyle();
                        style.BorderBottom = BorderStyle.Thin;
                        style.BottomBorderColor = HSSFColor.Black.Index;
                        style.BorderLeft = BorderStyle.Thin;
                        style.LeftBorderColor = HSSFColor.Black.Index;
                        style.BorderRight = BorderStyle.Thin;
                        style.RightBorderColor = HSSFColor.Black.Index;
                        style.BorderTop = BorderStyle.Thin;
                        style.TopBorderColor = HSSFColor.Black.Index;
                        cell.CellStyle = style;
                    }

                }

            }
        }
        #endregion


    }
}
